package com.tangdi.mkfile.gzdwmx;
import java.io.File;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import com.tangdi.ReportFileInfo;
import com.tangdi.Utils;
import com.tangdi.Bean.CZGZ_DWCOUNT;
import com.tangdi.Bean.CZGZ_GZLB;
import com.tangdi.Bean.CZGZ_SJGZ;
import com.tangdi.Bean.CZGZ_ZGGZMX;
import com.tangdi.Bean.XM_BM_XM_MC;
import com.tangdi.Dao.impl.GongziMXImpl;

public class CreateDWGZMXExcel_OneByOne {
	private Logger logger = Logger.getLogger(CreateDWGZMXExcel_OneByOne.class);
	private String excel_url_root = Utils.getFilePath("root_path");
	private String title_content = "工  资  发  放  明  细  表";
	private String folder_Name = ReportFileInfo.FOLDER_NAME_GZMX.getName();
	private String file_Type = "excel";
	private String file_name_type = ReportFileInfo.FILE_NAME_GZMX.getName();
	
	public int createExcel(Map<String,List<XM_BM_XM_MC>> map_bm_mc_list,List<CZGZ_DWCOUNT> listDWCOUNT,List<CZGZ_GZLB> list_gz_lb,String month) {
		StringBuffer sql_select = null;
		StringBuffer sql_select_mx = null;
		String sql_select_lb_count = null;
		String sql_select_lb_mx = null;
		int initSize = 0;
		int excel_rows = 0;
		HSSFWorkbook wb = null;
		Sheet sheet = null;
	    try {
			GongziMXImpl gzmx = new GongziMXImpl();
			String excel_url = "";
			//listDWCOUNT.size()
			for(int i = 0; i < listDWCOUNT.size(); i++){
				if(i == 0){
					wb = new HSSFWorkbook();
					sheet = wb.createSheet("sheet1");
					excel_rows = 0;
					excel_url = excel_url_root + "/" + month + "/" + file_Type + "/" + folder_Name + "/" + month + "_"+ file_name_type + "_" + listDWCOUNT.get(i).getDW_DM() +"_" + listDWCOUNT.get(i).getDW_MC() + ".xls";
					//在填写文件路径时，一定要写上具体的文件名称（xx.txt），否则会出现拒绝访问。
					File file = new File(excel_url);
					if(!file.exists()){
					    //先得到文件的上级目录，并创建上级目录，在创建文件
						file.getParentFile().mkdirs();
						file.createNewFile();
					}
				}
				//字体
				Font headerFont = getFont(wb, "微软雅黑", 18);
				Font contextFont = getFont(wb, "微软雅黑", 12);
				//样式 
				CellStyle headerStyle = getCellStyle(wb, "noboder", CellStyle.ALIGN_CENTER, headerFont);
				CellStyle contentStyle = getCellStyle(wb, "noboder", CellStyle.ALIGN_LEFT, contextFont);
				CellStyle contentStyle2 = getCellStyle(wb, "noboder", CellStyle.ALIGN_RIGHT, contextFont);
				CellStyle nameStyle = getCellStyle(wb, "border", CellStyle.ALIGN_CENTER, contextFont);
				CellStyle valueStyle_left = getCellStyle(wb, "border", CellStyle.ALIGN_LEFT, contextFont);
				CellStyle valueStyle_right = getCellStyle(wb, "border", CellStyle.ALIGN_RIGHT, contextFont);
				
				String gz_lb = listDWCOUNT.get(i).getGZ_LB_DM();
				String gz_dw_dm = listDWCOUNT.get(i).getDW_DM();
				
				//查找不全为空值的列，为空的列舍弃，只保留不为空的列 start
				sql_select = new StringBuffer("SELECT ");
				List<XM_BM_XM_MC> list_BM_MC = map_bm_mc_list.get(listDWCOUNT.get(i).getGZ_LB_DM());
				List<XM_BM_XM_MC> list_BM_MC_temp = new ArrayList<XM_BM_XM_MC>();
				
				logger.info(listDWCOUNT.get(i).getDW_MC() + "--" + listDWCOUNT.get(i).getGZ_LB_DM() + " | " +list_BM_MC.size());
				for(int m = 0; m < list_BM_MC.size(); m++){
					//sql_select.append("NVL(SUM("+ list_BM_MC.get(m).getXM_BM() +"),'0') AS " +list_BM_MC.get(m).getXM_BM() + ",");
					sql_select.append("IFNULL(to_number(SUM("+ list_BM_MC.get(m).getXM_BM() +")),'0') AS " +list_BM_MC.get(m).getXM_BM() + ",");
					}
				sql_select_lb_count = sql_select.substring(0, sql_select.length() - 1);

				//按工资类别、单位代码，汇总工资明细各科目的值
				CZGZ_ZGGZMX gzDW_Count = new CZGZ_ZGGZMX();
				gzDW_Count = gzmx.getCountGZByLB_DW(month, gz_dw_dm, gz_lb, list_BM_MC, sql_select_lb_count);
				
				//删除均为空的列
				Iterator<XM_BM_XM_MC> it_bm_mc = list_BM_MC.iterator();
				while(it_bm_mc.hasNext()){
					XM_BM_XM_MC bm_mc = it_bm_mc.next();
					String value_temp = (String)Utils.getFieldReflect(gzDW_Count,CZGZ_ZGGZMX.class,bm_mc.getXM_BM());
					if(!((value_temp.trim()).equals("0.00") || (value_temp.trim()).equals("0"))){
						list_BM_MC_temp.add(bm_mc);
					}
				}
				
				//查找不全为空值的列，为空的列舍弃，只保留不为空的列 end				
				//根据有值的列查明细
				initSize = list_BM_MC_temp.size();
				initSize = initSize + 2;
				
				sql_select_mx = new StringBuffer("SELECT TRIM(YF) AS YF, TRIM(NME) AS NME, ");
				for(int m = 0; m < list_BM_MC_temp.size(); m++){
					sql_select_mx.append("TRIM("+ list_BM_MC_temp.get(m).getXM_BM() +") AS " +list_BM_MC_temp.get(m).getXM_BM() + ",");
				}
				sql_select_lb_mx = sql_select_mx.substring(0, sql_select_mx.length() - 1);
				
				//工资明细list
				List<CZGZ_ZGGZMX> listmx=new ArrayList<CZGZ_ZGGZMX>();				
				listmx = gzmx.listGZMXReportByLB(month, gz_dw_dm, gz_lb,list_BM_MC_temp, sql_select_lb_mx);
				int listmx_Size = listmx.size();
				
				for(int a = 0; a < initSize; a++){
					sheet.setColumnWidth(a, 15*256);
			    }
				sheet.setColumnWidth(0, 5*256);
				//第 1 行  标题
				Row row00 = sheet.createRow(0 + excel_rows);
				addCell_Value(row00, 0, title_content, headerStyle);
				//第 2 行  标题
				Row row01 = sheet.createRow(1 + excel_rows);
				addCell_Value(row01, 0, "单位名称：" + listDWCOUNT.get(i).getDW_MC(), contentStyle);
				addCell_Value(row01, initSize - 2, month.substring(0, 4) + "年" + month.substring(4, 6)+ "月", contentStyle2);
				//第 3 行  列名 start
				Row row02 = sheet.createRow(2 + excel_rows);
				addCell_Value(row02, 0, "序号", nameStyle);
				addCell_Value(row02, 1, "姓名", nameStyle);				
				for(int h = 0; h < list_BM_MC_temp.size(); h++){//显示工资科目名称
					addCell_Value(row02, h + 2, list_BM_MC_temp.get(h).getXM_MC(), nameStyle);
				}
				//列名--end
				
				for(int j = 0; j < listmx_Size; j++){
					CZGZ_ZGGZMX gz_mx = listmx.get(j);
	                //列值 --start
					Row rowTemp = sheet.createRow(j + 3 + excel_rows);
					addCell_Value(rowTemp, 0, String.valueOf(j + 1), nameStyle);
					addCell_Value(rowTemp, 1, gz_mx.getNME(), valueStyle_left);
					for(int k = 0; k < list_BM_MC_temp.size(); k++){//根据工资类别反射查找工资科目
						String gz_value = (String)Utils.getFieldReflect(gz_mx,CZGZ_ZGGZMX.class,list_BM_MC_temp.get(k).getXM_BM());
						addCell_Value(rowTemp, k + 2, (((gz_value.trim()).equals("0") || (gz_value.trim()).equals("0.00")) ? " " : (gz_value.trim())), valueStyle_right);
					}
					//列值 --end
				}
				
				//单位合计 start
				Row row20 = sheet.createRow(listmx_Size + 3 + excel_rows);
				addCell_Value(row20, 0, String.valueOf(listmx_Size + 1), nameStyle);
				addCell_Value(row20, 1, "合 计", nameStyle);
				for(int k = 0; k < list_BM_MC_temp.size(); k = k + 2){//汇总各科目金额 up
					String gz_value = (String)Utils.getFieldReflect(gzDW_Count,CZGZ_ZGGZMX.class,list_BM_MC_temp.get(k).getXM_BM());
					String gz_value2 = ((gz_value.trim()).equals("0") || (gz_value.trim()).equals("0.00")) ? " " : (gz_value.trim());
					addCell_Value(row20, k + 2, gz_value2, valueStyle_left);
					if((k + 3) < list_BM_MC_temp.size() + 2){
						addCell_Value(row20, k + 3, "", valueStyle_left);
					}
				}
				/*if((initSize % 2) != 0){//如果为奇数 补上最后cell
					addCell_Value(row20, initSize - 1, "", valueStyle_right);
				}*/
				
				Row row21 = sheet.createRow(listmx_Size + 4 + excel_rows);
				addCell_Value(row21, 0, "", nameStyle);
				addCell_Value(row21, 1, "", nameStyle);
				for(int k = 1; k < list_BM_MC_temp.size(); k = k + 2){//汇总各科目金额 down
					String gz_value = (String)Utils.getFieldReflect(gzDW_Count,CZGZ_ZGGZMX.class,list_BM_MC_temp.get(k).getXM_BM());
					String gz_value2 = ((gz_value.trim()).equals("0") || (gz_value.trim()).equals("0.00")) ? " " : (gz_value.trim());
					addCell_Value(row21, k + 1, gz_value2, valueStyle_right);
					addCell_Value(row21, k + 2, "", valueStyle_right);
				}
				if((initSize % 2) != 0){//如果为奇数 补上最后cell
					addCell_Value(row21, initSize - 1, "", valueStyle_right);
				}
				//单位合计 end				
				Row row22 = sheet.createRow(listmx_Size + 6 + excel_rows);
				addCell_Value(row22, 0, "银行付讫（盖章）：", contentStyle);
				addCell_Value(row22, (int)Math.ceil(initSize / 2), "制表人：", contentStyle);

				sheet.addMergedRegion(new CellRangeAddress(excel_rows, excel_rows, 0, initSize - 1));
				sheet.addMergedRegion(new CellRangeAddress(excel_rows + 1, excel_rows + 1, 0, initSize - 3));
				sheet.addMergedRegion(new CellRangeAddress(excel_rows + 1, excel_rows + 1, initSize - 2, initSize -1));

				sheet.addMergedRegion(new CellRangeAddress(listmx_Size + 3 + excel_rows, listmx_Size + 4 + excel_rows, 0, 0));
				sheet.addMergedRegion(new CellRangeAddress(listmx_Size + 3 + excel_rows, listmx_Size + 4 + excel_rows, 1, 1));
				
				for(int s = 0; s < (int)Math.ceil((initSize-2) / 2); s++){
					sheet.addMergedRegion(new CellRangeAddress(listmx_Size + 3 + excel_rows, listmx_Size + 3 + excel_rows, s*2 + 2, s*2 + 3));
					sheet.addMergedRegion(new CellRangeAddress(listmx_Size + 4 + excel_rows, listmx_Size + 4 + excel_rows, s*2 + 2, s*2 + 3));
				}
				
				sheet.addMergedRegion(new CellRangeAddress(listmx_Size + 6 + excel_rows, listmx_Size + 6 + excel_rows, 0, (int)Math.ceil(initSize / 2) - 1));
				sheet.addMergedRegion(new CellRangeAddress(listmx_Size + 6 + excel_rows, listmx_Size + 6 + excel_rows, (int)Math.ceil(initSize / 2), initSize - 1));
				
				if(i == (listDWCOUNT.size() - 1)){
					FileOutputStream os = new FileOutputStream(excel_url);
					wb.write(os);
					os.close();
				}else{
					if(!(listDWCOUNT.get(i).getDW_DM()).equals(listDWCOUNT.get(i+1).getDW_DM())){
						FileOutputStream os = new FileOutputStream(excel_url);
						wb.write(os);
						os.close();

						wb = new HSSFWorkbook();
						sheet = wb.createSheet("sheet1");
						excel_rows = 0;
						excel_url = excel_url_root + "/" + month + "/" + file_Type + "/" + folder_Name + "/" + month + "_"+ file_name_type + "_" + listDWCOUNT.get(i + 1).getDW_DM() +"_" + listDWCOUNT.get(i + 1).getDW_MC() + ".xls";
						//在填写文件路径时，一定要写上具体的文件名称（xx.txt），否则会出现拒绝访问。
						File file = new File(excel_url);
						if(!file.exists()){
						    //先得到文件的上级目录，并创建上级目录，在创建文件
							file.getParentFile().mkdirs();
							file.createNewFile();
						}
					}else{
							excel_rows = excel_rows + listmx_Size + 10;
						}
					}
			}
	    } catch (Exception e) {
	    	e.printStackTrace();
	    	return -1;
		}
	    return 0;
	}
	public static CellStyle getCellStyle(HSSFWorkbook wb, String borderType, short align, Font font){
		CellStyle style = wb.createCellStyle();
		style.setAlignment(align);
		if(borderType.equals("border")){
			style.setBorderTop(CellStyle.BORDER_THIN);
			style.setBorderBottom(CellStyle.BORDER_THIN);
			style.setBorderLeft(CellStyle.BORDER_THIN);
			style.setBorderRight(CellStyle.BORDER_THIN);
		}
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
		style.setLocked(true);
		style.setWrapText(true);
		style.setFont(font);
		return style;
	}
	public static Font getFont(HSSFWorkbook wb, String fontName, int fontSize){
		Font font = wb.createFont();
		font.setFontName(fontName);
		font.setFontHeightInPoints((short)fontSize);
		font.setBoldweight(Font.BOLDWEIGHT_NORMAL);
		return font;
	}
	public static void addCell_Value(Row row, int a, String value, CellStyle cellStyle){
		Cell cell = row.createCell(a);
		cell.setCellValue(value);
		cell.setCellStyle(cellStyle);
	}
	public static void main(String[] args) {
		//年月
		String month = "201905";
		//根据单位编号、工资类别统计工资列表
		List<CZGZ_DWCOUNT> listDWCOUNT=new ArrayList<CZGZ_DWCOUNT>();
		GongziMXImpl gz_dw_count = new GongziMXImpl();
		listDWCOUNT = gz_dw_count.listGZDWCount(month);
		
		//工资类别，科目项信息
		Map<String,List<XM_BM_XM_MC>> map_bm_mc_list = new HashMap<String,List<XM_BM_XM_MC>>();
		
		//工资类别list
		List<CZGZ_GZLB> listlb=new ArrayList<CZGZ_GZLB>();
		GongziMXImpl gzlb = new GongziMXImpl();
		listlb = gzlb.listGZLBReport(month);
		
		//实际工资科目 按工资类别
		List<CZGZ_SJGZ> list_sjgz=new ArrayList<CZGZ_SJGZ>();
		GongziMXImpl sjgz = new GongziMXImpl();
		list_sjgz = sjgz.listSJGZReport(month);
		
		//组装工资类别 ，实际工资科目项，对应好关系；方便后续使用
		for(int i=0;i<listlb.size();i++){
			List<XM_BM_XM_MC> bm_mc_list = new ArrayList<XM_BM_XM_MC>();
			System.out.println(listlb.get(i).getLB_BM().trim());
			for(int j=0; j<list_sjgz.size(); j++){
				XM_BM_XM_MC bm_mc= new XM_BM_XM_MC();
				if((listlb.get(i).getLB_BM().trim()).equals(list_sjgz.get(j).getGZ_LB().trim())){					
					bm_mc.setXM_BM(Utils.getXM_BM(list_sjgz.get(j).getXM_BM().trim()));					
					bm_mc.setXM_MC(list_sjgz.get(j).getXM_MC().trim());					
					System.out.print(Utils.getXM_BM(list_sjgz.get(j).getXM_BM().trim()) + " | ");	
					bm_mc_list.add(bm_mc);
				}
			}
			System.out.println();
			map_bm_mc_list.put(listlb.get(i).getLB_BM().trim(), bm_mc_list);
		}
		
		CreateDWGZMXExcel_OneByOne c = new CreateDWGZMXExcel_OneByOne();
	    c.createExcel(map_bm_mc_list,listDWCOUNT,listlb,month);
	    System.out.println("制作完成");
	}
}
